#!/bin/bash

source /etc/profile

if [ -n "$1" ] ;then
        do_date=$1
else
        do_date=`date -d "-1 day" +%F`
fi


#计算最近7天

#从今天开始往后算7天，包括今天
start_date=$do_date
end_date=`date -d  "$start_date  6 day" +%F`



sql="select
	uid, 
	count(1) as continous_login_day_cnt
from 
(
	select
	uid,
	dt,
    	--dt-rownumber=gid(一个日期) 如果日期相等则为同一组
	date_sub(dt,row_number() over(partition by uid order by dt)) as gid
	from 
	dws.dws_member_start_day
	where dt >= '$start_date' and dt <= '$end_date'
) A
group by uid,gid --根据uid,gid分组
having continous_login_day_cnt>=3;"

echo $sql
hive -e "$sql"
